/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Categoria;
/**
 * @author Administrador
 */
public class AdminCategoria implements CategoriaManager{
    private Connection c;

    public AdminCategoria(Connection c){
        this.c = c;
    }
    
    public int getIdCategoria() throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDCATEGORIA)+1,1) AS ID FROM CATEGORIA";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminCategoria -> getIdCategoria() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    @Override
    public void saveCategoria(Categoria ca) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "INSERT INTO CATEGORIA VALUES("+ca.getIdCategoria();
            query += ", '"+ca.getNombre().toUpperCase().trim();
            query += "', '"+ca.getSigla().toUpperCase().trim()+"',"+ca.isActivo()+")";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminCategoria saveCategoria(Categoria ca) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public void updateCategoria(Categoria ca) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE CATEGORIA SET NOMBRE = '"+ca.getNombre().toUpperCase().trim();
            query+= "', SIGLA = '"+ca.getSigla().toUpperCase().trim();
            query+= "' WHERE IDCATEGORIA ="+ca.getIdCategoria();
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminCategoria -> updateCategoria(Categoria ca) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public List<Categoria> getCategorias() throws DataException{
        List<Categoria> categorias = new ArrayList<Categoria>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDCATEGORIA, NOMBRE, SIGLA";
            query += " FROM CATEGORIA WHERE ACTIVO = 1";
            query += " ORDER BY NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Categoria c = new Categoria();
                c.setIdCategoria(rs.getInt("IDCATEGORIA"));
                c.setNombre(rs.getString("NOMBRE"));
                c.setSigla(rs.getString("SIGLA"));
                categorias.add(c);
            }
        }catch(SQLException e){
            throw new DataException(" AdminCategoria -> getCategorias() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return categorias;
    }
    
    public Categoria getCategoria(int idCategoria) throws DataException{
        Categoria ca = new Categoria();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT NOMBRE, SIGLA, ACTIVO";
            query += " FROM CATEGORIA WHERE IDCATEGORIA = "+idCategoria;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                ca.setIdCategoria(idCategoria);
                ca.setNombre(rs.getString("NOMBRE"));
                ca.setSigla(rs.getString("SIGLA"));
                ca.setActivo(rs.getBoolean("ACTIVO"));
            }
        }catch(SQLException e){
            throw new DataException(" AdminCategoria -> getCategoria() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return ca;
    }
    
    public List<Categoria> getCategoriasDadosDeBaja() throws DataException{
        List<Categoria> categorias = new ArrayList<Categoria>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDCATEGORIA, NOMBRE, SIGLA";
            query += " FROM CATEGORIA";
            query += " WHERE ACTIVO = 0";
            query += " ORDER BY NOMBRE";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Categoria c = new Categoria();
                c.setIdCategoria(rs.getInt("IDCATEGORIA"));
                c.setNombre(rs.getString("NOMBRE"));
                c.setSigla(rs.getString("SIGLA"));
                categorias.add(c);
            }
        }catch(SQLException e){
            throw new DataException(" AdminCategoria -> getPaisesDadosDeBaja() e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return categorias;
    }
    
    public boolean isUseInDetalleDoc(int idCategoria) throws DataException{
        boolean res = false;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IDDETALLE_DOCENTE";
        query += " FROM DETALLE_DOCENTE";
        query += " WHERE IDCATEGORIA = "+idCategoria;
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next())
                res = true;
        }catch(SQLException e){
            throw new DataException(" AdminCategoria -> isUseInDetalleDoc() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    public void deleteCategoria(int idCategoria) throws DataException {
        PreparedStatement preSt = null;
        String query = "DELETE FROM CATEGORIA WHERE IDCATEGORIA="+idCategoria;
        try{
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException("Error! en AdminPais -> deleteCategoria(int idCategoria) e = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void darBaja(int idCategoria) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE CATEGORIA SET ACTIVO = 0 WHERE IDCATEGORIA = "+idCategoria;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminCategoria -> darBaja(int idCategoria) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public void darAlta(int idCategoria) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE CATEGORIA SET ACTIVO = 1 WHERE IDCATEGORIA = "+idCategoria;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminCategoria -> darAlta(int idCategoria) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
}
